package com.agency8x.model;

import com.agency8x.model.base.BaseMemberModel;
import com.agency8x.util.DateTimeUtil;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Page;

/**
 * Member model.
 * 数据库字段名建议使用驼峰命名规则，便于与 java 代码保持一致，如字段名： userId
 */
@SuppressWarnings("serial")
public class MemberModel extends BaseMemberModel<MemberModel> {
	
	public static final MemberModel me = new MemberModel().dao();
	
	/**
	 * 所有 sql 与业务逻辑写在 Service 中，在此放在 Model 中仅为示例
	 */
	public Page<MemberModel> paginate(int pageNumber, int pageSize, String userId,String date1, String date2) {
		String sql ="select a.user_id,a.nickname,a.mobile,a.realname,a.is_agent,a.regist_time,a.user_status,b.blance "+
				",c.withdraw_amount_total,c.recharge_amount_total,d.child1_all_count,d.child2_all_count ";
		String sqlFrom = "from t_user a left join t_user_finance_account b on a.user_id=b.user_id "+
		" left join t_user_finance_statistics c on a.user_id =c.user_id " +
		" left join t_user_referral_statistics d on a.user_id=d.user_id where a.is_agent=0 and a.nickname!='' ";
		if(StrKit.notBlank(userId)){
			if(isNumber(userId))
				sqlFrom = sqlFrom + " and a.user_id ="+userId;
			else
				sqlFrom = sqlFrom + "and (a.user_name like '%" + userId + "%' or a.nickname like '%" + userId + "%')";
		}
		 if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
	        	sqlFrom += " and a.regist_time >='" + date1 + " 0:0:0'";
	        }
	        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
	        	sqlFrom += " and a.regist_time <='" + date2 + " 23:59:59'";
	        }
		sqlFrom = sqlFrom +" order by a.regist_time desc ";
		return paginate(pageNumber, pageSize, sql,sqlFrom);
	}
	
	/**
	 * 所有 sql 与业务逻辑写在 Service 中，在此放在 Model 中仅为示例
	 */
	public Page<MemberModel> pagNoname(int pageNumber, int pageSize, String userId,String date1, String date2) {
		String sql ="select a.user_id,a.nickname,a.mobile,a.realname,a.is_agent,a.regist_time,a.user_status,b.blance "+
				",c.withdraw_amount_total,c.recharge_amount_total,d.child1_all_count,d.child2_all_count ";
		String sqlFrom = "from t_user a left join t_user_finance_account b on a.user_id=b.user_id "+
				" left join t_user_finance_statistics c on a.user_id =c.user_id " +
				" left join t_user_referral_statistics d on a.user_id=d.user_id where a.is_agent=0 and a.nickname='' ";
		if(StrKit.notBlank(userId)){
			if(isNumber(userId))
				sqlFrom = sqlFrom + " and a.user_id ="+userId;
			else
				sqlFrom = sqlFrom + "and (a.user_name like '%" + userId + "%' or a.nickname like '%" + userId + "%')";
		}
		if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
        	sqlFrom += " and a.regist_time >='" + date1 + " 0:0:0'";
        }
        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
        	sqlFrom += " and a.regist_time <='" + date2 + " 23:59:59'";
        }
		sqlFrom = sqlFrom +" order by a.regist_time desc ";
		return paginate(pageNumber, pageSize, sql,sqlFrom);
	}
	
	/**
	 * 一级代理
	 */
	public Page<MemberModel> pagchild1(int pageNumber, int pageSize, String user_id,String search_word) {
		String sql ="select a.user_id,a.nickname,a.mobile,a.realname,a.is_agent,a.regist_time,a.user_status,b.blance "+
				",c.withdraw_amount_total,c.recharge_amount_total,d.child1_all_count,d.child2_all_count ";
		String sqlFrom = "from t_user a left join t_user_finance_account b on a.user_id=b.user_id "+
				" left join t_user_finance_statistics c on a.user_id =c.user_id " +
				" left join t_user_referral_statistics d on a.user_id=d.user_id where 1=1 ";
		if(user_id!=null&&!"".equals(user_id.trim())){
			sqlFrom = sqlFrom + " and a.parent1_uid ="+user_id;
		}
		if(search_word!=null && !"".equals(search_word.trim()) && !"null".equals(search_word.trim())){
			sqlFrom = sqlFrom + " and a.user_id ="+search_word;
		}
		sqlFrom = sqlFrom +" order by a.regist_time desc ";
		return paginate(pageNumber, pageSize, sql,sqlFrom);
	}
	
	/**
	 * 二级代理
	 */
	public Page<MemberModel> pagchild2(int pageNumber, int pageSize, String user_id,String search_word) {
		String sql ="select a.user_id,a.nickname,a.mobile,a.realname,a.is_agent,a.regist_time,a.user_status,b.blance "+
				",c.withdraw_amount_total,c.recharge_amount_total,d.child1_all_count,d.child2_all_count ";
		String sqlFrom = "from t_user a left join t_user_finance_account b on a.user_id=b.user_id "+
				" left join t_user_finance_statistics c on a.user_id =c.user_id " +
				" left join t_user_referral_statistics d on a.user_id=d.user_id where 1=1 ";
		if(user_id!=null&&!"".equals(user_id.trim())){
			sqlFrom = sqlFrom + " and a.parent2_uid ="+user_id;
		}
		if(search_word!=null&&!"".equals(search_word.trim()) && !"null".equals(search_word.trim())){
			sqlFrom = sqlFrom + " and a.user_id ="+search_word;
		}
		sqlFrom = sqlFrom +" order by a.regist_time desc ";
		return paginate(pageNumber, pageSize, sql,sqlFrom);
	}
	
	public MemberModel agenEdit(String where) {
		return me.findFirst("select * from t_user where user_id = ?", where);
	}
	
	public static boolean isNumber(String str){  
		boolean re = true;  
		for(int i=0;i<str.length();i++){  
			if(!Character.isDigit(str.charAt(i))){  
				re = false;  
				break;  
			}  
		}  
		return re;  
	} 
	
}

